package ar.edu.utn.frgp.banco.dao.Implements;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ar.edu.utn.frgp.banco.dao.Interfaces.IntPagoPrestamo;
import ar.edu.utn.frgp.banco.dao.connection.ImpConectionHandler;
import ar.edu.utn.frgp.banco.dao.Implements.ImpPrestamos;
import ar.edu.utn.frgp.banco.domain.Cliente;
import ar.edu.utn.frgp.banco.domain.Cuenta;
import ar.edu.utn.frgp.banco.domain.PagoPrestamo;
import ar.edu.utn.frgp.banco.domain.Prestamos;
import ar.edu.utn.frgp.banco.exceptions.DataAccessException;

public class ImpPagoPrestamo implements IntPagoPrestamo {

	@Override
	public void AltaPagoPrestamo(PagoPrestamo pagoprestamo)
			throws DataAccessException {
		PreparedStatement ps = null;
		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();

		try {

			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("Insert into Pagoprestamos (id_prestamo,id_cuenta, ");
			stringBuilder.append("fecha, monto, origen, nrocuota) ");
			stringBuilder.append("VALUES (?,?,?,?,?,?)");

			ps = cn.prepareStatement(stringBuilder.toString());

			ps.setInt(1, pagoprestamo.getId_prestamo());
			ps.setInt(2, pagoprestamo.getId_cuenta());
			ps.setDate(3, new java.sql.Date(pagoprestamo.getFecha().getTime()));
			ps.setDouble(4, pagoprestamo.getMonto());
			ps.setString(5, pagoprestamo.getOrigen());
			ps.setInt(6, pagoprestamo.getNrocuota());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(ps, cn);
		}

	}

	@Override
	public PagoPrestamo TraePagoPrestamo(Integer id_pagoprestamo)
			throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		PagoPrestamo pagopre = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("Select id_pagoprestamo,id_prestamo,id_cuenta, ");
			stringBuilder.append("fecha, monto, origen, nrocuota ");
			stringBuilder.append("from pagoprestamos ");
			stringBuilder.append("WHERE id_pagoprestamo = ?");

			ps = cn.prepareStatement(stringBuilder.toString());
			ps.setInt(1, id_pagoprestamo);

			rs = ps.executeQuery();

			while (rs.next()) {
				pagopre = new PagoPrestamo();

				pagopre.setId_pagoprestamo(rs.getInt("id_pagoprestamo"));
				pagopre.setId_prestamo(rs.getInt("id_prestamo"));
				pagopre.setId_cuenta(rs.getInt("id_cuenta"));
				pagopre.setFecha(rs.getDate("fecha"));
				pagopre.setMonto(rs.getDouble("monto"));
				pagopre.setOrigen(rs.getString("origen"));
				pagopre.setNrocuota(rs.getInt("nrocuota"));

			}

		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pagopre;
	}

	@Override
	public List<PagoPrestamo> TraePagoPrestamo(Cuenta cuenta)
			throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<PagoPrestamo> pagoprestamos = new ArrayList<PagoPrestamo>();

		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("Select id_pagoprestamo,id_prestamo,id_cuenta, ");
			stringBuilder.append("fecha, monto, origen, nrocuota ");
			stringBuilder.append("from pagoprestamos ");
			stringBuilder.append("WHERE id_cuenta = ?");

			ps = cn.prepareStatement(stringBuilder.toString());
			ps.setInt(1, cuenta.getID_Cuenta());

			rs = ps.executeQuery();
			PagoPrestamo pagopre = new PagoPrestamo();
			while (rs.next()) {
				pagopre = new PagoPrestamo();

				pagopre.setId_pagoprestamo(rs.getInt("id_pagoprestamo"));
				pagopre.setId_prestamo(rs.getInt("id_prestamo"));
				pagopre.setId_cuenta(rs.getInt("id_cuenta"));
				pagopre.setFecha(rs.getDate("fecha"));
				pagopre.setMonto(rs.getDouble("monto"));
				pagopre.setOrigen(rs.getString("origen"));
				pagopre.setNrocuota(rs.getInt("nrocuota"));

				pagoprestamos.add(pagopre);
			}
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pagoprestamos;
	}

	@Override
	public List<PagoPrestamo> TraePagoPrestamo(Cliente cliente)
			throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Prestamos> prestamos = null;
		List<PagoPrestamo> pagoprestamos = new ArrayList<PagoPrestamo>();
		ImpPrestamos prestamosdao = new ImpPrestamos();
		prestamos = prestamosdao.TraerPrestamos(cliente);

		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();

		try {
			for (Prestamos prestamo : prestamos) {

				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder
						.append("Select id_pagoprestamo,id_prestamo,id_cuenta, ");
				stringBuilder.append("fecha, monto, origen, nrocuota ");
				stringBuilder.append("from pagoprestamos ");
				stringBuilder.append("WHERE id_prestamo = ?");

				ps = cn.prepareStatement(stringBuilder.toString());
				ps.setInt(1, prestamo.getId_prestamo());

				rs = ps.executeQuery();
				PagoPrestamo pagopre = new PagoPrestamo();
				while (rs.next()) {
					pagopre = new PagoPrestamo();

					pagopre.setId_pagoprestamo(rs.getInt("id_pagoprestamo"));
					pagopre.setId_prestamo(rs.getInt("id_prestamo"));
					pagopre.setId_cuenta(rs.getInt("id_cuenta"));
					pagopre.setFecha(rs.getDate("fecha"));
					pagopre.setMonto(rs.getDouble("monto"));
					pagopre.setOrigen(rs.getString("origen"));
					pagopre.setNrocuota(rs.getInt("nrocuota"));

					pagoprestamos.add(pagopre);
				}

			}

		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pagoprestamos;
	}

	@Override
	public List<PagoPrestamo> TraePagoPrestamos(Prestamos prestamo)
			throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<PagoPrestamo> pagoprestamos = new ArrayList<PagoPrestamo>();

		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("Select id_pagoprestamo,id_prestamo,id_cuenta, ");
			stringBuilder.append("fecha, monto, origen, nrocuota ");
			stringBuilder.append("from pagoprestamos ");
			stringBuilder.append("WHERE id_prestamo = ?");

			ps = cn.prepareStatement(stringBuilder.toString());
			ps.setInt(1, prestamo.getId_prestamo());

			rs = ps.executeQuery();
			PagoPrestamo pagopre = new PagoPrestamo();
			while (rs.next()) {
				pagopre = new PagoPrestamo();

				pagopre.setId_pagoprestamo(rs.getInt("id_pagoprestamo"));
				pagopre.setId_prestamo(rs.getInt("id_prestamo"));
				pagopre.setId_cuenta(rs.getInt("id_cuenta"));
				pagopre.setFecha(rs.getDate("fecha"));
				pagopre.setMonto(rs.getDouble("monto"));
				pagopre.setOrigen(rs.getString("origen"));
				pagopre.setNrocuota(rs.getInt("nrocuota"));

				pagoprestamos.add(pagopre);
			}
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pagoprestamos;
	}

	@Override
	public PagoPrestamo TraeUltimoPagoPrestamos(Integer id_prestamo)
			throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		PagoPrestamo pagopre = null;

		try {
			
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("Select id_pagoprestamo,id_prestamo,id_cuenta, ");
			stringBuilder.append("fecha, monto, origen, nrocuota ");
			stringBuilder.append("from pagoprestamos  ");
			stringBuilder.append("WHERE id_prestamo = 42 and ");
			stringBuilder.append("nrocuota  = (select max(nrocuota) from  ");
			stringBuilder.append("pagoprestamos WHERE id_prestamo = ?) ");

			ps = cn.prepareStatement(stringBuilder.toString());
			ps.setInt(1, id_prestamo);

			rs = ps.executeQuery();
			while (rs.next()) {
				pagopre = new PagoPrestamo();
				pagopre.setId_pagoprestamo(rs.getInt("id_pagoprestamo"));
				pagopre.setId_prestamo(rs.getInt("id_prestamo"));
				pagopre.setId_cuenta(rs.getInt("id_cuenta"));
				pagopre.setFecha(rs.getDate("fecha"));
				pagopre.setMonto(rs.getDouble("monto"));
				pagopre.setOrigen(rs.getString("origen"));
				pagopre.setNrocuota(rs.getInt("nrocuota"));
			}
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pagopre;
	}
}